package es.juanc.poi;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.StringTokenizer;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class ExcelManager {
	
	private static final String DEFAULT_SEPARATOR = "#";
	private static final int MAX_ROWS_XLS = 65536;
	private static final String XLS = ".xls";
	
	private static final ExcelManager INSTANCE = new ExcelManager();
	
	private ExcelManager() {
		super();
	}
	
	public static ExcelManager getInstance() {
		return INSTANCE;
	}
	
	/**
	 * Genera fichero excell
	 * 
	 * @param rows Lista con todas las filas de la hoja de calculo.
	 *			En cada fila las columnas van separadas por el caracter 'separator'
	 * @param separator Separador usado para las columnas.
	 * @param sheetName Nombre de la hoja.
	 * @param path Ruta en el que se deja el fichero generado.
	 * @param fileName Nombre del fichero xls generado.
	 * 
	 * @return Ruta al archivo generado.
	 * 
	 * @throws IOException
	 */
	public File generateXlsFile( List rows, String separator, String sheetName, String path, String fileName ) throws IOException {
				
		if( separator == null ) {
			separator = DEFAULT_SEPARATOR;
		}

		HSSFWorkbook book = new HSSFWorkbook();
		
		HSSFFont font = book.createFont();
		HSSFCellStyle fontStyle = book.createCellStyle();
		fontStyle.setFont( font );

		int len = rows.size() / MAX_ROWS_XLS;
		if( len != 0 ) {
			int from = 0;
			int to = MAX_ROWS_XLS;
			for( int i = 0 ; len >= i ; i++ ) {
				from = i * MAX_ROWS_XLS;
				to = i * MAX_ROWS_XLS + (rows.size() < i*MAX_ROWS_XLS+MAX_ROWS_XLS ? rows.size()%MAX_ROWS_XLS : MAX_ROWS_XLS);			
				createSheet( rows.subList( from, to ), book, fontStyle, sheetName, separator );
			}
		} else {
			createSheet( rows, book, fontStyle, sheetName, separator );
		}

		File pathFile = new File( path );
		pathFile.mkdirs();
		
		File xlsFile = new File( pathFile, fileName.endsWith( XLS ) ? fileName : fileName+XLS );		
		FileOutputStream xlsStream = new FileOutputStream( xlsFile );			
		book.write( xlsStream );
		xlsStream.close();
		
		return xlsFile;
	}
	
	private void createSheet( List rows, HSSFWorkbook book, HSSFCellStyle fontStyle, String sheetName, String separator ) {
		
		HSSFSheet sheet;
		if( sheetName != null ) {
			sheet = book.createSheet( sheetName );
		} else {
			sheet = book.createSheet();
		}
		
		int j = 0;
		for( Iterator i = rows.iterator() ; i.hasNext() ; j++ ) {
			HSSFRow row = sheet.createRow( j );				 
			StringTokenizer tokens = new StringTokenizer( (String)i.next(), separator );
			for( int k = 0 ; tokens.hasMoreTokens() ; k++ ) {
				String token = tokens.nextToken();
				HSSFCell cell = row.createCell( (short)k );
//				if( j == 0 && header != null ) {
//					HSSFFont headerFont = book.createFont();
//					headerFont.setBoldweight( HSSFFont.BOLDWEIGHT_BOLD );
//					HSSFCellStyle bold = book.createCellStyle();
//					bold.setFont( headerFont );
//					cell.setCellStyle( bold );
//				} else {
				cell.setCellStyle( fontStyle );
//				}
				HSSFRichTextString text = new HSSFRichTextString( token.trim() );
				cell.setCellValue( text );
			}
		}
	}
	
	public File generateXlsFile( List rows, String separator, String path, String xlsName ) throws IOException {
		return generateXlsFile( rows, null, separator, path, xlsName );
	}
	
	public File generateXlsFile( List rows, String path, String xlsName ) throws IOException {
		return generateXlsFile( rows, DEFAULT_SEPARATOR, path, xlsName );
	}
	
	public static void main( String[] args ) throws Exception {
		
		List report = new ArrayList();
		report.add( "uno#dos#tres#cuatro#cinco" );
		report.add( "xxx#yyy" );
		report.add( "xxx#yyy#zzz#kkk#ppp" );
		report.add( "xxx#yyy#zzz" );
		report.add( "xxx#yyy#zzz#kkk" );
		
		System.out.println(	ExcelManager.getInstance()
								.generateXlsFile( report, "prueba.xls", "" )
								.getAbsolutePath() );

//		long num1 = MAX_ROWS_XLS-1;
//		long num2 = MAX_ROWS_XLS;
//		long num3 = MAX_ROWS_XLS+1;
//		long num4 = 150000;
//		
//		System.out.println( num1 / MAX_ROWS_XLS + " " + num1 % MAX_ROWS_XLS );
//		System.out.println( num2 / MAX_ROWS_XLS + " " + num2 % MAX_ROWS_XLS );
//		System.out.println( num3 / MAX_ROWS_XLS + " " + num3 % MAX_ROWS_XLS );
//		System.out.println( num4 / MAX_ROWS_XLS + " " + num4 % MAX_ROWS_XLS );
		
//		int x = MAX_ROWS_XLS;
//		List l = new ArrayList();
//		for( int i = 0 ; i < x ; i++ ) {
//			l.add( "x" );
//		}
//		System.out.println( l.size() );		
//		int len = l.size() / MAX_ROWS_XLS;
//		int j = 0;
//		for( int i = 0 ; len >= i ; i++ ) {
//			List ll = l.subList( i*MAX_ROWS_XLS, i*MAX_ROWS_XLS+(l.size()<i*MAX_ROWS_XLS+MAX_ROWS_XLS?l.size()%MAX_ROWS_XLS:MAX_ROWS_XLS) );
//			System.out.println( i + " -> " + ll.size() );
//			j += ll.size();
//		}
//		System.out.println( j );	
	}
}